/***********************************************************************************************************************************
 *    Name:	Job_Stats.sql
 *  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
 * Summary:	
**/
USE msdb;
WITH latest (job_id, last_run) AS (
	SELECT job_id, MAX(dbo.agent_datetime(run_date,run_time))
	  FROM dbo.sysjobhistory
	  WHERE step_id = 0 AND run_status IN (0, 1, 3)
	  GROUP BY job_id)
SELECT
	[Job Name]	= j.[name],
	[Start Time]= CAST(l.last_run AS DATETIME2(0)),
	[Duration]	= h.run_duration/10000*3600+(h.run_duration%10000)/100*60+h.run_duration%100,
	[Status]	= CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE Null END,
	[Retries]	= h.retries_attempted,
	[Age]		= DATEDIFF(hh, l.last_run,CURRENT_TIMESTAMP)
  FROM dbo.sysjobs j INNER JOIN latest l ON (l.job_id = j.job_id)
	INNER JOIN dbo.sysjobhistory h ON (l.job_id = h.job_id AND l.last_run = dbo.agent_datetime(h.run_date,h.run_time) AND h.step_id = 0)
  WHERE j.[enabled] = 1
  ORDER BY h.run_status, j.[name]
GO